package dao;

import java.sql.*;
import java.util.ArrayList;
import bean.ThucVatHeSinhThai;
import bean.ThucVatLamSanModel;
import bean.ThucVatLamSanNgoaiGoModel;
import bean.ThucVatSanHoModel;
import bean.ThucVatThamCoBienModel;
import bean.ThucVatTrongTrotModel;

public class ThucVatProcessDAO {
	private ConnectDatabaseDAO connectDatabaseDAO = new ConnectDatabaseDAO();
	private Connection con;
	private Statement st;
	
	// close connect
	private void closeConnect() throws SQLException {
		con.close();
		st.close();
		System.out.println("Closed connect");
	}
	//---------------------------------THUC VAT------------------------------------------
	public ArrayList<ThucVatHeSinhThai> listInfoThucVatHeSinhThai() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<ThucVatHeSinhThai> listThucVatHeSinhThai = new ArrayList<ThucVatHeSinhThai>();
		try {
			st = con.createStatement();
			String sql = "SELECT hsttv.idhstthucvat, hsttv.tenloaikhoahoc, hsttv.tenloaidiaphuong, hsttv.tenloailatin,"
       +" hsttv.tenhokhoahoc, hsttv.tenhodiaphuong, hsttv.tenholatin, hsttv.giatri, hsttv.mota, hsttv.loaisinhtruong, image, count(hsttv.idhstthucvat) as soluong"
       +" FROM hstthucvat hsttv left outer join thucvat_lamsan tvls on hsttv.idhstthucvat = tvls.idhstthucvat left outer join thucvat_lamsan_vitri tvlsvt"
       +" on tvls.idthucvat_lamsan = tvlsvt.idthucvat_lamsan left outer join thucvat_lamsanngoaigo tvlsng on hsttv.idhstthucvat = tvlsng.idhstthucvat left outer join thucvat_lamsanngoaigo_vitri tvlsngvt"
       +" on tvlsng.idthucvat_lamsanngoaigo = tvlsngvt.idthucvat_lamsanngoaigo left outer join thucvat_sanho tvsh on hsttv.idhstthucvat = tvsh.idhstthucvat left outer join thucvat_sanho_vitri tvshvt"
       +" on tvsh.idthucvat_sanho = tvshvt.idthucvat_sanho left outer join thucvat_thamcobien tvtcb on hsttv.idhstthucvat = tvtcb.idhstthucvat left outer join thucvat_thamcobien_vitri tvtcbvt"
       +" on tvtcb.idthucvat_thamcobien = tvtcbvt.idthucvat_thamcobien left outer join thucvat_trongtrot tvtt on hsttv.idhstthucvat = tvtt.idhstthucvat left outer join thucvat_trongtrot_vitri tvttvt"
       +" on tvtt.idthucvat_trongtrot = tvttvt.idthucvat_trongtrot group by hsttv.idhstthucvat, hsttv.tenloaikhoahoc, hsttv.tenloaidiaphuong, hsttv.tenloailatin, "
       +" hsttv.tenhokhoahoc, hsttv.tenhodiaphuong, hsttv.tenholatin, hsttv.giatri, hsttv.mota, hsttv.loaisinhtruong, image;";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				ThucVatHeSinhThai thucVatHeSinhThai = new ThucVatHeSinhThai();				
				thucVatHeSinhThai.setIdHSTThucVat(rs.getString("idhstthucvat"));
				thucVatHeSinhThai.setTenLoaiKhoaHoc(rs.getString("tenloaikhoahoc"));
				thucVatHeSinhThai.setTenLoaiDiaPhuong(rs.getString("tenloaidiaphuong"));
				thucVatHeSinhThai.setTenLoaiLaTin(rs.getString("tenloailatin"));
				thucVatHeSinhThai.setTenHoKhoaHoc(rs.getString("tenhokhoahoc"));
				thucVatHeSinhThai.setTenHoDiaPhuong(rs.getString("tenhodiaphuong"));
				thucVatHeSinhThai.setTenHoLaTin(rs.getString("tenholatin"));
				thucVatHeSinhThai.setGiaTri(rs.getString("giatri"));
				thucVatHeSinhThai.setMoTa(rs.getString("mota"));
				thucVatHeSinhThai.setLoaiSinhTruong(rs.getString("loaisinhtruong"));
				thucVatHeSinhThai.setImage(rs.getString("image"));
				thucVatHeSinhThai.setSoLuong(rs.getInt("soluong"));
				
				listThucVatHeSinhThai.add(thucVatHeSinhThai);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listThucVatHeSinhThai;
	}
	
	public ArrayList<ThucVatLamSanModel> listInfoThucVatLamSanModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<ThucVatLamSanModel> listThucVatLamSanModel = new ArrayList<ThucVatLamSanModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT * from thucvat_lamsan";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				ThucVatLamSanModel thucVatLamSanModel = new ThucVatLamSanModel();				
				thucVatLamSanModel.setIdThucVatLamSan(rs.getString("idthucvat_lamsan"));
				thucVatLamSanModel.setIdHSTThucVat(rs.getString("idhstthucvat"));
				thucVatLamSanModel.setChieuCao(rs.getFloat("chieucao"));
				thucVatLamSanModel.setDuongKinh(rs.getString("duongkinh"));
				thucVatLamSanModel.setTang(rs.getString("tang"));
				thucVatLamSanModel.setDangSong(rs.getString("dangsong"));
				thucVatLamSanModel.setDoChePhuDongTay(rs.getFloat("dochephudongtay"));
				thucVatLamSanModel.setDoChePhuNamBac(rs.getFloat("dochephunambac"));
				thucVatLamSanModel.setDoTuoi(rs.getFloat("dotuoi"));
				thucVatLamSanModel.setNamKhaoSat(rs.getString("namkhaosat"));
				
				listThucVatLamSanModel.add(thucVatLamSanModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listThucVatLamSanModel;
	}
	
	public ArrayList<ThucVatLamSanNgoaiGoModel> listInfoThucVatLamSanNgoaiGoModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<ThucVatLamSanNgoaiGoModel> listThucVatLamSanNgoaiGo = new ArrayList<ThucVatLamSanNgoaiGoModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT * from thucvat_lamsanngoaigo";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				ThucVatLamSanNgoaiGoModel thucVatLamSanNgoaiGoModel = new ThucVatLamSanNgoaiGoModel();				
				thucVatLamSanNgoaiGoModel.setIdThucVatLamSanNgoaiGo(rs.getString("idthucvat_lamsanngoaigo"));
				thucVatLamSanNgoaiGoModel.setIdHSTThucVat(rs.getString("idhstthucvat"));
				thucVatLamSanNgoaiGoModel.setChieuCao(rs.getFloat("chieucao"));
				thucVatLamSanNgoaiGoModel.setTang(rs.getString("tang"));
				thucVatLamSanNgoaiGoModel.setDoTuoi(rs.getFloat("dotuoi"));
				thucVatLamSanNgoaiGoModel.setNamKhaoSat(rs.getString("namkhaosat"));
				
				listThucVatLamSanNgoaiGo.add(thucVatLamSanNgoaiGoModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listThucVatLamSanNgoaiGo;
	}
	
	public ArrayList<ThucVatSanHoModel> listInfoThucVatSanHoModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<ThucVatSanHoModel> listThucVatSanHoModel = new ArrayList<ThucVatSanHoModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT * from thucvat_sanho";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				ThucVatSanHoModel thucVatSanHoModel = new ThucVatSanHoModel();				
				thucVatSanHoModel.setIdThucVatSanHo(rs.getString("idthucvat_sanho"));
				thucVatSanHoModel.setIdHSTThucVat(rs.getString("idhstthucvat"));
				thucVatSanHoModel.setNguonGocTaiSinh(rs.getString("nguongoctaisinh"));
				thucVatSanHoModel.setChatLuongSinhTruong(rs.getString("chatluongsinhtruong"));
				thucVatSanHoModel.setChieuCao(rs.getFloat("chieucao"));
				thucVatSanHoModel.setDoTuoi(rs.getFloat("dotuoi"));
				thucVatSanHoModel.setNamKhaoSat(rs.getString("namkhaosat"));
				
				listThucVatSanHoModel.add(thucVatSanHoModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listThucVatSanHoModel;
	}
	
	public ArrayList<ThucVatThamCoBienModel> listInfoThucVatThamCoBienModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<ThucVatThamCoBienModel> listThucVatThamCoBienModel = new ArrayList<ThucVatThamCoBienModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT * from thucvat_thamcobien";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				ThucVatThamCoBienModel thucVatThamCoBienModel = new ThucVatThamCoBienModel();				
				thucVatThamCoBienModel.setIdThucVatThamCoBien(rs.getString("idthucvat_thamcobien"));
				thucVatThamCoBienModel.setIdHSTThucVat(rs.getString("idhstthucvat"));
				thucVatThamCoBienModel.setNguonGocTaiSinh(rs.getString("nguongoctaisinh"));
				thucVatThamCoBienModel.setChatLuongSinhTruong(rs.getString("chatluongsinhtruong"));
				thucVatThamCoBienModel.setChieuCao(rs.getFloat("chieucao"));
				thucVatThamCoBienModel.setDoTuoi(rs.getFloat("dotuoi"));
				thucVatThamCoBienModel.setNamKhaoSat(rs.getString("namkhaosat"));
				
				listThucVatThamCoBienModel.add(thucVatThamCoBienModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listThucVatThamCoBienModel;
	}
	
	public ArrayList<ThucVatTrongTrotModel> listInfoThucVatTrongTrotModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<ThucVatTrongTrotModel> listThucVatTrongTrotModel = new ArrayList<ThucVatTrongTrotModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT * from thucvat_trongtrot";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				ThucVatTrongTrotModel thucVatTrongTrotModel = new ThucVatTrongTrotModel();	
				thucVatTrongTrotModel.setIdThucVatTrongTrot(rs.getString("idthucvat_trongtrot"));
				thucVatTrongTrotModel.setIdHSTThucVat(rs.getString("idhstthucvat"));
				thucVatTrongTrotModel.setNguonGocTaiSinh(rs.getString("nguongoctaisinh"));
				thucVatTrongTrotModel.setChatLuongSinhTruong(rs.getString("chatluongsinhtruong"));
				thucVatTrongTrotModel.setChieuCao(rs.getFloat("chieucao"));
				thucVatTrongTrotModel.setNangSuat(rs.getString("nangsuat"));
				thucVatTrongTrotModel.setNamKhaoSat(rs.getString("vumua"));
				thucVatTrongTrotModel.setNamKhaoSat(rs.getString("namkhaosat"));
				
				listThucVatTrongTrotModel.add(thucVatTrongTrotModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listThucVatTrongTrotModel;
	}
	
	public ArrayList<ThucVatLamSanModel> listPointThucVatLamSanModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<ThucVatLamSanModel> listThucVatLamSanModel = new ArrayList<ThucVatLamSanModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT idthucvat_lamsan_vitri, idthucvat_lamsan, ST_X(\"ViTri\")" 
				+ " as longitude, ST_Y(\"ViTri\") as latitude, bankinh, matdo  FROM thucvat_lamsan_vitri;";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				ThucVatLamSanModel thucVatLamSanModel = new ThucVatLamSanModel();				
				thucVatLamSanModel.setIdTVLamSanViTri(rs.getString("idthucvat_lamsan_vitri"));
				thucVatLamSanModel.setIdThucVatLamSan(rs.getString("idthucvat_lamsan"));
				thucVatLamSanModel.setViTri_Latitude(rs.getDouble("latitude"));
				thucVatLamSanModel.setVitri_Longitude(rs.getDouble("longitude"));
				thucVatLamSanModel.setBankinh(rs.getFloat("bankinh"));
				thucVatLamSanModel.setMatdo(rs.getString("matdo"));
				
				listThucVatLamSanModel.add(thucVatLamSanModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listThucVatLamSanModel;
	}
	
	public ArrayList<ThucVatLamSanNgoaiGoModel> listPointThucVatLamSanNgoaiGoModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<ThucVatLamSanNgoaiGoModel> listThucVatLamSanNgoaiGo = new ArrayList<ThucVatLamSanNgoaiGoModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT idthucvat_lamsanngoaigo_vitri, idthucvat_lamsanngoaigo, ST_X(\"ViTri\")" 
				+ " as longitude, ST_Y(\"ViTri\") as latitude, bankinh, matdo FROM thucvat_lamsanngoaigo_vitri;";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				ThucVatLamSanNgoaiGoModel thucVatLamSanNgoaiGoModel = new ThucVatLamSanNgoaiGoModel();				
				thucVatLamSanNgoaiGoModel.setIdTVLamSanNgoaiGoViTri(rs.getString("idthucvat_lamsanngoaigo_vitri"));
				thucVatLamSanNgoaiGoModel.setIdThucVatLamSanNgoaiGo(rs.getString("idthucvat_lamsanngoaigo"));
				thucVatLamSanNgoaiGoModel.setViTri_Latitude(rs.getDouble("latitude"));
				thucVatLamSanNgoaiGoModel.setVitri_Longitude(rs.getDouble("longitude"));
				thucVatLamSanNgoaiGoModel.setBankinh(rs.getFloat("bankinh"));
				thucVatLamSanNgoaiGoModel.setMatdo(rs.getString("matdo"));
				
				listThucVatLamSanNgoaiGo.add(thucVatLamSanNgoaiGoModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listThucVatLamSanNgoaiGo;
	}
	
	public ArrayList<ThucVatSanHoModel> listPointThucVatSanHoModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<ThucVatSanHoModel> listThucVatSanHoModel = new ArrayList<ThucVatSanHoModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT idthucvat_sanho_vitri, idthucvat_sanho, ST_X(\"ViTri\")" 
				+ " as longitude, ST_Y(\"ViTri\") as latitude, bankinh, matdo FROM thucvat_sanho_vitri;";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				ThucVatSanHoModel thucVatSanHoModel = new ThucVatSanHoModel();				
				thucVatSanHoModel.setIdTVSanHoViTri(rs.getString("idthucvat_sanho_vitri"));
				thucVatSanHoModel.setIdThucVatSanHo(rs.getString("idthucvat_sanho"));
				thucVatSanHoModel.setViTri_Latitude(rs.getDouble("latitude"));
				thucVatSanHoModel.setVitri_Longitude(rs.getDouble("longitude"));
				thucVatSanHoModel.setBankinh(rs.getFloat("bankinh"));
				thucVatSanHoModel.setMatdo(rs.getString("matdo"));
				
				listThucVatSanHoModel.add(thucVatSanHoModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listThucVatSanHoModel;
	}
	
	public ArrayList<ThucVatThamCoBienModel> listPointThucVatThamCoBienModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<ThucVatThamCoBienModel> listThucVatThamCoBienModel = new ArrayList<ThucVatThamCoBienModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT idthucvat_thamcobien_vitri, idthucvat_thamcobien, ST_X(\"ViTri\")" 
				+ " as longitude, ST_Y(\"ViTri\") as latitude, bankinh, matdo FROM thucvat_thamcobien_vitri;";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				ThucVatThamCoBienModel thucVatThamCoBienModel = new ThucVatThamCoBienModel();				
				thucVatThamCoBienModel.setIdTVThamCoBienViTri(rs.getString("idthucvat_thamcobien_vitri"));
				thucVatThamCoBienModel.setIdThucVatThamCoBien(rs.getString("idthucvat_thamcobien"));
				thucVatThamCoBienModel.setViTri_Latitude(rs.getDouble("latitude"));
				thucVatThamCoBienModel.setVitri_Longitude(rs.getDouble("longitude"));
				thucVatThamCoBienModel.setBankinh(rs.getFloat("bankinh"));
				thucVatThamCoBienModel.setMatdo(rs.getString("matdo"));
				
				listThucVatThamCoBienModel.add(thucVatThamCoBienModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listThucVatThamCoBienModel;
	}
	
	public ArrayList<ThucVatTrongTrotModel> listPointThucVatTrongTrotModel() throws SQLException{
		con = connectDatabaseDAO.connect();
		ArrayList<ThucVatTrongTrotModel> listThucVatTrongTrotModel = new ArrayList<ThucVatTrongTrotModel>();
		try {
			st = con.createStatement();
			String sql = "SELECT idthucvat_trongtrot_vitri, idthucvat_trongtrot, ST_X(\"ViTri\")" 
				+ " as longitude, ST_Y(\"ViTri\") as latitude, bankinh, matdo FROM thucvat_trongtrot_vitri;";
			ResultSet rs = st.executeQuery(sql);
			while(rs.next()){
				ThucVatTrongTrotModel thucVatTrongTrotModel = new ThucVatTrongTrotModel();	
				thucVatTrongTrotModel.setIdTVTrongTrotViTri(rs.getString("idthucvat_trongtrot_vitri"));
				thucVatTrongTrotModel.setIdThucVatTrongTrot(rs.getString("idthucvat_trongtrot"));
				thucVatTrongTrotModel.setViTri_Latitude(rs.getDouble("latitude"));
				thucVatTrongTrotModel.setVitri_Longitude(rs.getDouble("longitude"));
				thucVatTrongTrotModel.setBankinh(rs.getFloat("bankinh"));
				thucVatTrongTrotModel.setMatdo(rs.getString("matdo"));
				
				listThucVatTrongTrotModel.add(thucVatTrongTrotModel);
			}
		} catch (Exception e) {
			System.out.println("Can't database excute");
		} finally{
			closeConnect();
		}
		return listThucVatTrongTrotModel;
	}
	
}
